<!DOCTYPE html>
<html  lang="zh">
<head>
    <!-- hexo-inject:begin --><!-- hexo-inject:end --><meta charset="utf-8" />

<meta name="generator" content="Hexo 3.9.0" />

<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />

<title>mysql学习-1 - 游戏人生</title>


    <meta name="description" content="MySql">
<meta name="keywords" content="学习">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql学习-1">
<meta property="og:url" content="http://cloundgame.gitee.io/mystudy/2020/07/28/mysql学习-1/index.html">
<meta property="og:site_name" content="游戏人生">
<meta property="og:description" content="MySql">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="http://cloundgame.gitee.io/mystudy/images/og_image.png">
<meta property="og:updated_time" content="2020-07-27T16:02:13.354Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="mysql学习-1">
<meta name="twitter:description" content="MySql">
<meta name="twitter:image" content="http://cloundgame.gitee.io/mystudy/images/og_image.png">







<link rel="icon" href="/mystudy/images/favicon.svg">


<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bulma@0.7.2/css/bulma.css">
<link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/font-awesome/5.9.0/css/all.min.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Ubuntu:400,600|Source+Code+Pro">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/highlight.js@9.12.0/styles/atom-one-dark.css">


    
    
    
    <style>body>.footer,body>.navbar,body>.section{opacity:0}</style>
    

    
    
    
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/lightgallery@1.6.8/dist/css/lightgallery.min.css">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/justifiedGallery@3.7.0/dist/css/justifiedGallery.min.css">
    

    
    

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/outdatedbrowser@1.1.5/outdatedbrowser/outdatedbrowser.min.css">


    
    
    
    

<link rel="stylesheet" href="/mystudy/css/back-to-top.css">


    
    

    
    
    
    

    
    
<link rel="stylesheet" href="/mystudy/css/progressbar.css">
<script src="https://cdn.jsdelivr.net/npm/pace-js@1.0.2/pace.min.js"></script>

    
    
    
        <script async="" src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    

    


<link rel="stylesheet" href="/mystudy/css/style.css"><!-- hexo-inject:begin --><!-- hexo-inject:end -->
</head>
<body class="is-3-column">
    <!-- hexo-inject:begin --><!-- hexo-inject:end --><nav class="navbar navbar-main">
    <div class="container">
        <div class="navbar-brand is-flex-center">
            <a class="navbar-item navbar-logo" href="/mystudy/">
            
                <img src="/mystudy/images/logo.svg" alt="mysql学习-1" height="28">
            
            </a>
        </div>
        <div class="navbar-menu">
            
            <div class="navbar-start">
                
                <a class="navbar-item"
                href="/mystudy/">主页</a>
                
                <a class="navbar-item"
                href="/mystudy/archives">日志</a>
                
                <a class="navbar-item"
                href="/mystudy/categories">分类</a>
                
                <a class="navbar-item"
                href="/mystudy/">关于</a>
                
            </div>
            
            <div class="navbar-end">
                
                    
                    
                    <a class="navbar-item" target="_blank" title="Download on GitHub" href="https://github.com/koliy/koliy.github.io">
                        
                        <i class="fab fa-github"></i>
                        
                    </a>
                    
                
                
                <a class="navbar-item is-hidden-tablet catalogue" title="目录" href="javascript:;">
                    <i class="fas fa-list-ul"></i>
                </a>
                
                
                <a class="navbar-item search" title="搜索" href="javascript:;">
                    <i class="fas fa-search"></i>
                </a>
                
            </div>
        </div>
    </div>
</nav>
    
    <section class="section">
        <div class="container">
            <div class="columns">
                <div class="column is-8-tablet is-8-desktop is-6-widescreen has-order-2 column-main"><div class="card">
    
    <div class="card-content article ">
        
        <div class="level article-meta is-size-7 is-uppercase is-mobile is-overflow-x-auto">
            <div class="level-left">
                <time class="level-item has-text-grey" datetime="2020-07-27T16:01:02.000Z">2020-07-28</time>
                
                <div class="level-item">
                <a class="has-link-grey -link" href="/mystudy/categories/MySql/">MySql</a>
                </div>
                
                
                <span class="level-item has-text-grey">
                    
                    
                    1 小时 读完 (大约 7074 个字)
                </span>
                
                
                <span class="level-item has-text-grey" id="busuanzi_container_page_pv">
                    <i class="far fa-eye"></i>
                    <span id="busuanzi_value_page_pv">0</span>次访问
                </span>
                
            </div>
        </div>
        
        <h1 class="title is-size-3 is-size-4-mobile has-text-weight-normal">
            
                mysql学习-1
            
        </h1>
        <div class="content">
            <h2 id="mysql"><a href="#mysql" class="headerlink" title="mysql"></a><strong>mysql</strong></h2><a id="more"></a>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//变量类型<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//整型<br>bool:<br>    1字节<br>tinyint：<br>    1字节,-128<del>127<br>smallint:<br>    2字节,-32768</del>32767<br>int:<br>    4字节<br>bigint:<br>    8字节</p>
<p>//指定数据类型显示宽度:<br>    int(4):<br>//浮点型<br>float:<br>    4字节<br>    float(m,d)<br>double:<br>    8字节<br>DEC(M,D):<br>    8字节，同double，可以精确长度和小数点后保留位<br>//指定数据类型的精度:<br>    float(6,2):<br>    //总长度为6，不包括小数点。小数点后保留2位：1234.12<br>    //小数点后保留位会进行四舍五入</p>
<p>//日期时间类型<br>time：<br>    00:00:00, //now()函数可以获取系统时间<br>date:<br>    YYYY-MM-DD,//now()函数可以获取系统日期<br>datetime:<br>    YYYY-MM-DD HH:MM:SS //now()函数可以获取系统日期和时间<br>timestamp:<br>    //时间int值，输入null，自动保存系统当前日期时间</p>
<p>//字符串类型<br>char:<br>    //分配字符长度字节固定。<br>varchar:<br>    //分配字符长度字节可变，根据实际字符数分配内存大小+1<br>如：<br>    “1” : char(5) 占5个字节， varchar(5):占2个字节，字符串末尾结束标志位自动加”\0”</p>
<p>text:<br>    //长文本</p>
<p>blob:<br>    //存储图片，pdf二进制数据</p>
<p>enum：<br>    // name enum(‘xiao’, ‘xiao1’, ‘xiao2’)<br>    // 带 not null, 则自动取第一个值，不带则可以置null</p>
<p>==================================<br>注释：<br>    #show databases;<br>    /**/</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//创建数据库<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>1：create database  dataname； //创建数据库<br>2：show databases\G;    //显示所有数据库<br>3: drop database dataname;  //删除数据库<br>4：use dataname；  //选择当前数据操作<br>5：show engines\G；  //显示当前数据库支持的存储引擎<br>6：desc listname;  //在对应数据库中，显示指定的表的属性字段<br>7: show tables； //在当前数据库中显示所有的表<br>8：select * from list; //显示指定表的所有数据。<br>9：truncate table list; //清空表的所有数据<br>10. show indexes from list; //查看表的唯一约束</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//创建，修改，删除表: alter<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//约束条件<br>primary key :主键<br>foreign key : 外键<br>not null : 变量不为空<br>key：主要是用来加快查询速度的。<br>unique:  变量值是唯一<br>unique key:<br>    唯一约束，保证数据的唯一性，可以设置多个字段。<br>auto_increment: 变量的值自动加1<br>default： 设置默认值<br>comment：注释<br><code>create</code> :使用关键字做字段，用反引号。</p>
<p>//创建表<br>create table list()DEFAULT CHARSET=utf8; //创建一个默认字符集编码为utf8的表</p>
<p>create table listname( varname  int  not null unique primary key,<br>        varname2  varchar(30) not null default ‘aaa’<br>        varname3 int default 1);<br>//创建主键<br>create table listname(varname int, varname2 int, primary key( varname, varname2));<br>    保证每条记录的唯一性，自动为NOT NULL，一个表只能有一个主键<br>    自带索引，最高性能的，用于快速搜索筛选。</p>
<p>//创建外键<br>create table listname(varname int primary key, constraint f_name foreign key(varname) references listname2(var1));<br>说明：子表的外键关联的必须是父表的主键<br>    保证数据的一致性，比如注册用户表和用户数据表<br>    子表和父表必须存储引擎一样，为InnoDB<br>    数据类型必须一致，字符类型的长度可以不同。</p>
<p>//自增长<br>create table list( id int primary key auto_increment);<br>    必须与主键组合使用，既自增长的字段必须定义为主键，<br>    默认情况下起始值为1.<br>    添加记录时，可以不用设置此字段了，由数据库维护。<br>    数据库会保存最大的字段值，删除记录后，不会清除最大值，下次增加仍以最大值添加。</p>
<p>//重置自增长初始值：<br>    alter table list auto_increment=1;//从1开始 ，0无效<br>//注释：<br>    create table list(id int comment ‘主id’) comment ‘商店表’;</p>
<p>所谓utf8_unicode_ci，其实是用来排序的规则。<br>对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，<br>都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。<br>简而言之，COLLATE会影响到ORDER BY语句的顺序，<br>会影响到WHERE条件中大于小于号筛选出来的结果，会影响<strong>DISTINCT</strong>、<strong>GROUP BY</strong>、<strong>HAVING</strong>语句的查询结果。<br>另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，<br>只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。</p>
<p>//查看指定表<br>desc listname;<br>show create table listname\G;<br>//删除表<br>drop table listname;</p>
<p>//修改表<br>//修改表名:rename<br>alter table listname rename listname1;<br>//修改表属性的类型: modify<br>alter table listname modify  varname int(10);<br>//修改表属性的名字: change<br>alter table listname change varname varname int not null;<br>//增加属性: add<br>alter table listname add varname int not null after vaname1;<br>alter table listname add varname int first varname1;<br>//删除属性<br>alter table listname drop varname;<br>//删除外键<br>alter table listname drop forign key f_name;<br>//修改表属性的前后位置<br>alter table listname modify varname int after vaname2;<br>//修改表的存储引擎<br>alter table listname engine = MyISAM;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//索引:index<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>说明:索引，用来快速查询数据库表中的特定记录<br>unique:<br>fulltext:全文本索引，只能用在MyISAM引擎上</p>
<p>//创建索引<br>create table listname (varname int, index alias_name(varname));<br>create table listname (varname int, unique index alias_name(varname ASC));<br>create table listname(varname varchar(20),fulltext index alias_name(varname))engine=MyISAM;</p>
<p>create unique index alias_name  on listname(vaname1);<br>alter table listname add unique index alias_name(vaname1);</p>
<p>//创建单列索引<br>create table listname(varname varchar(20), index alias_name(varname(10)));<br>对于字符串数据，可以不用查询全部数据，只查询前10个字符</p>
<p>//删除索引<br>drop index alias_name on listname;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//视图:view<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>说明：视图是从一个表或多个表中选出需要的属性来构建的虚拟表，隐藏其它属性，只<br>      显示给客户需要关注的属性数据。保证了数据库的安全性<br>数据库中只存放了视图的定义，而并没有存放视图中的数据。因此视图中的数据是依赖于原来的表<br>中的数据。一旦表中的数据发生变化，视图中的数据也会变化。</p>
<p>//创建视图<br>create view view_name(vaname1,vaname2) as select varname1 ,varname2 from listname1 ,listname2;</p>
<p>//多表上创建视图<br>create view view_name(vaname1，vaname2) as select var1, listname2.var1 from listname1,listname2;</p>
<p>//查看视图<br>desc view_name;<br>show create view view_name\G;</p>
<p>//所有视图的定义保存在information_schema.views表中。<br>select * from information.schema.views\G;</p>
<p>//修改 or 创建视图<br>create or replace view view_name(var) as select var1 from listname;<br>alter view view_name(var) as select var1 from listname;</p>
<p>//更新视图属性的数据值，更新视图的值最后都是更新到依赖的原始表上<br>update view_name set var1=’111’;<br>1:视图更新无法用于表中含有算法公式的视图表:<br>SUM() count() max() min()<br>create view view_name(var) as select count(var1) from listname;<br>2:无法更新union, distinct,group by, havig<br>create view view_name(var) as select var1 from listname group by id;<br>3:常量视图<br>4:视图中的select中包含子查询<br>create view view_name(var) as select (select name from listname2);<br>5:由不可更新的视图导出的视图</p>
<p>//新增表属性的数据值<br>insert into listname values(1,’aaa’);<br>insert into list values(NULL/DEFAULT,’aaa’); //NULL/DEFAULT 让数据库管理自己的约束，不控制它<br>insert list set id=’1’ name=’xiao’;<br>insert list1(id,name) select * from list2;</p>
<p>//查询表的属性的数据值<br>select * from listname;<br>select * from listname where id = 1;</p>
<p>//删除表的属性的数据值<br>delete from listname where id =1 ;<br>delete from listname; //删除整个表数据, 不会清除自增约束的值。<br>//清空恢复初始值<br>truncate table list;</p>
<p>//删除视图<br>drop view if exists view_name ,view_name1 ;<br>假如存在视图表就删除，不存在不删除。<br>========================================================<br>创建临时表<br>    用于缓存数据，避免频繁连接数据表查询数据。断开连接后自动清除临时表数据。<br>create temporary table list{};</p>
<p>复制表<br>create table list1 like list2;<br>    只复制结构，不复制数据<br>create table list1 as select * from list2;<br>    复制结构和数据，一些约束会丢弃，主键，自增，unqiue</p>
<p>==========================================================</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//触发器:trigger<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>说明:当insert ，update， delete语句执行时，数据库会激活触发事件，执行相应操作。<br>比如:当学生表中增加一个学生的信息时，学生的总数必须变化。</p>
<p>//创建触发器<br>create trigger trigger_name  before|after insert|update|delete<br>    on listname for each row<br>    执行语句</p>
<p>trigger_name：新创建的触发器名。<br>before: 触发事件之前执行触发语句<br>after:<br>for each row: 任何一条记录上的操作满足触发事件都会触发该触发器。</p>
<p>create trigger trigger_name before insert<br>    on listname for each row<br>    insert into listname2 values(now());<br>表示当向listname表中insert操作时，数据库都会在执行insert语句之前想 listname2 触发表中插入当前时间。<br>如：insert into listname values(1);<br>    select * from listname2;<br>//创建触发器多个执行语句 : begin … end<br>delimiter &amp;&amp;<br>使当前mysql的结束符位&amp;&amp;，不再是;符号，因此想结束当前语句，必须用&amp;&amp;替换;<br>创建多个执行语句时，用到了;。因此需要修改mysql的结束符号<br>mysql&gt; delimiter &amp;&amp;<br>mysql&gt; create trigger tg2 after delete on mylist for each row<br>     &gt; begin<br>     &gt; insert into trigger_time values(now()); //这里用到了;符号<br>     &gt; insert into trigger_time values(‘111’); //这里用到了;符号<br>     &gt; end<br>     &gt; &amp;&amp;   //注意现在必须以&amp;&amp;作结束符<br>mysql&gt; delimiter ;  //改回以; 符号作为结束符。 &amp;&amp;不在起作用了</p>
<p>create trigger update_sname after update on studentinfo for each row<br>begin<br>update mylist1 set sname = new.sname where id = new.id;<br>end;<br>–<br>new.sname: 表示studentinfo 更新的值。如insert,update</p>
<p>create trigger delete_stu after delete on studentinfo for each row<br>begin<br>delete from mylist where id = old.id;<br>end</p>
<hr>
<p>old.id: 表示新删除的记录的id数据</p>
<p>//查看触发器<br>所有的触发器都保存在information_schema数据库下的triggers的表里面<br>show triggers\G;<br>select * from information_schema.triggers;<br>select * from information_schema.triggers where trigger_name = ‘tg1’\G;</p>
<p>//删除触发器<br>drop trigger tg1;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//查询语句 :select … from<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></p>
<p>//条件查询<br>Where :<br>where id =1<br>where id &lt; 10<br>where name =’xiao’</p>
<p>//排序<br>order by 属性名 [ASC|DESC]<br>按照指定的属性名字段进行排序<br>//分组<br>Group by 属性名 [having 条件]<br>按照属性名的字段进行分组，如果带有 having，则只有满足条件才能再分组</p>
<p>例子：<br>select num,id,name, age from mylist where age &lt; 26  order by id desc；<br>从mylist表中找出age小于26岁的数据，然后根据id的值进行降序排列。</p>
<p>//条件表达式,不能比较字符串<br>=,&lt;,&lt;=,&gt;=,!=,<br>//指定范围<br>between … and<br>not between … and<br>属性 between  20 and 40<br>大于等于20 ， 小于等于40<br>属性 not between 20 and 40<br>小于20，或大于40</p>
<p>select name,age from mylist where age between 20 and 30;<br>//查询mylist age属性值是大于20小于40的数据</p>
<p>//指定集合<br>in<br>not in<br>in(100,200,300)<br>in(‘xiao’,’mi’)</p>
<p>select name,age from mylist where id in(1,2);<br>查询mylist表中 id是 1,2的数据</p>
<p>判断属性值是否匹配in集合里面某个值。</p>
<p>//匹配字符是否相等<br>like<br>not like<br>like ‘xiao’ ：全字符匹配比较<br>like ‘xi%’  ：% 任意通配符字符长度比较 ,以xi开头任意字符<br>like ‘xi_o’ : _ 单通配符字符比较,以xi开头1个任意字符</p>
<p>select name from mylist where name like ‘xia%’;</p>
<p>//判断是否为空值<br>is null<br>is not null<br>判断属性值是否为空<br>select name,age from mylist where age is null;</p>
<p>least(x,y,…)<br>    返回最小的值<br>greatest(x,y,…)<br>    返回最大的值</p>
<p>//多条件的与， 或<br>and<br>or<br>select name,age from mylist where age &lt;20 and name like ‘xi_o’；</p>
<p>//查询并显示结果不重复<br>distinct 属性名<br>    剔除属性值重复出现的数据<br>select distinct sex from mylist;<br>    查询sex不重复的数据</p>
<p>//排序查询： Order by  ASC|DESC</p>
<p>select * from mylist order by age;</p>
<p>//多属性排序：先按第一个属性排序，当第一个属性值相等时，则这些相等的数据里面再按第2个属性排序。<br>select * from mylist order by id ASC , age DESC;</p>
<p>//分组查询： group by<br>Group by 属性名 [having 条件表达式] [with rollup]<br>having: 满足此给定的条件才能显示。<br>with rollup: 在最后加上一条记录，该记录是上面所有记录的总和</p>
<p>//分组<br>select * from mylist group by sex;<br>只会显示分组的第一条记录数据。不会显示分组的所有数据。</p>
<p>select group_concat(name) from mylist group by sex;<br>显示每组所有的name数据</p>
<p>//统计每组的某个属性集合(count(): 个数, Min(),Max(),SUM():求和<br>AVG():平均数</p>
<p>//统计每组的记录的总条数:count<br>select sex ,count(sex) from mylist group by sex;</p>
<p>//统计每组的记录数据中的最大属性值的记录<br>select id, max(id) from mylist group by sex;<br>查询sex组中id最大的记录</p>
<p>//条件分组: having<br>select group_concat(name) from mylist group by sex having count(sex) =2;<br>查询每组sex相同记录达到2条的数据就显示出来<br>select group_concat(name) from mylist group by sex having sex like ‘M’;</p>
<p>//按多个属性字段分组:先按第一个字段分组，在第一个字段多相等的情况下，在根据第2个<br>select * from mylist group by age,sex;</p>
<p>//附加每组的总记录数: with rollup<br>配合count<br>select *,count(age) from mylist group by age with rollup;</p>
<p>//限制只查询几条结果数据:limit<br>select * from mylist limit 2;<br>限制只显示2条数据，当要求数量超出list的实际长度时，则显示list的实际长度数据</p>
<p>select * from mylist limit 0, 2<br>限制从第1条开始，显示2条数据</p>
<p>//count(): 集合函数，统计记录条数<br>//sum():某个字段的总和</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//连接查询<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>连接查询是将2个或以上的表按某个条件连接，从中选取需要的字段数据。<br>//内连接查询<br>显示左表及右表符合连接条件的记录<br>只有表中有意义相同的字段才能进行连接<br>select name,sex from mylist , mylist2 where mylist.id = mylist2.id;<br>inner … join … on/using(id)<br>select * from list1 inner join list2 using(matchid);</p>
<p>//外连接 ： join … on …<br>//左外连接<br>select name ,sex,funtion from mylist left join mylist2 on mylist.id = mylist.id;<br>查询mylist的所有数据，而mylist2的数据只有满足on的条件才能查询出来，没有的补NULL<br>显示左表的全部记录及右表符合连接条件的记录，右表不符合条件的显示null<br>右表的字段也会全部显示，只不过不符合条件的都显示null<br>select * from boys left join girls on boys.matchid = grils.matchid where girlnum is not null;</p>
<p>//右外连接<br> 同理，显示所有mylist2的所有数据，而mylist的数据只有满足ON的条件才出现，不足补null。<br> select name,sex,funtion from mylist right join mylist2 on mylist.id =mylist2.id;</p>
<p>//复合条件连接查询<br>select name,funtion from mylist,mylist2 where mylist.id = mylist2.id and age &gt;24;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//子查询<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//带in 的子查询<br>select * from employee where id in (select id from department);<br>查询id和department表中id值一样的所有数据</p>
<p>select * from employee where score &gt;= (select score from department where level=1);<br>//带Exists的子查询<br>存在子查询的记录，则返回true，外层查询则进行查询，false，不进行查询<br>select * from employee where exists (select * from department where id=1003);<br>假如department有id=1003的记录，返回true。则查询employee所有记录<br>select * from employee where age &gt;24 and exists (select * from department where id = 1003);<br>//not exists<br>不存在子查询的条件记录时返回true</p>
<p>//any 子查询<br>满足子查询记录里面任一值，返回true<br>select * from employee where age &gt;= any (select age from department);<br>显示employ表下age &gt;= department表中age任一值的所有数据( 1002,1004) &gt;= (1002,1003)<br>//all 子查询<br>只有满足子查询记录的所有值的条件，才返回true<br>select * from employee where id &gt; all (select id from department);<br>显示employee表中 id 大于department表中所有ID的记录数据(比如:1004 大于 department表中1001,1002,1003)<br>//union 合并子查询 ，查询的字段必须一样<br>将多个查询结果合并在一起显示，并剔除相同的记录<br>select id from employee<br>union<br>select id from department;</p>
<p>//union all<br>将多个查询结果合并一起显示，不剔除相同的记录<br>select id from employee<br>union all<br>select id from department;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//为表和字段取别名<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></p>
<p>//表取别名<br>select * from department d ;<br>select * from department d where d.id =1002;<br>select d.id from department d;</p>
<p>//字段取别名 ,用着显示的时候，不能用在where条件里。它只是显示时候出现<br>属性名 as 别名<br>select d_id as id from department;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//regexp 正则表达式查询<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>属性名 regexp ‘匹配方式’<br>select * from employee where name regexp ‘^L’;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//插入,更新，删除: insert , update, delete<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></p>
<p>//插入: insert into … values();<br>insert into mylist values(1,’2’,3);<br>不列出表里面的属性名，则values给所有字段赋值<br>insert into mylist(id,name) values(1,’2’);<br>列出表里面的属性名，则values根据指定的属性名赋值，并且列出的属性名排放的位置可以任意。<br>没指定的属性名的值，会根据default自动给定默认值，或者null<br>//同时插入多条语句<br>insert into mylist values(1,’2’),(2,’3’);<br>insert into mylist(id,name) values(1,’2’),(2,’3’);</p>
<p>//将select查询的结果插入到insert的表中<br>insert into …. select …<br>insert into mylist(id,name) select id,name from mylist2 where id &gt; 100;<br>从mylist2表中查询出大于100的记录id,name数据插入到表mylist中<br>必须保证各自的属性类型都是一样</p>
<p>//更新 ： update … set … where<br>update mylist set id = 1,name=’a’ where id = 1000;</p>
<p>//删除数据 : delete from … where<br>delete from mylist where id =100;<br>delete from mylist; //删除所有数据</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//运算符<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>+，-，*，/,%</p>
<p>select a,a+5,a-1,a*2,a/2 from mylist;<br>将表mylist中a的值进行运算符操作，然后显示</p>
<p>// =， != 运算符<br>用来判断数字，字符串，表达式是否相等，是返回1,不是返回0<br>不能判断NULL<br>select age =1,name =’xiao’,sex != ‘M’ from mylist;</p>
<p>// is null, is not null<br>判断操作数是否为null，是返回 1， 不是返回 0<br>select sex is null from mylist；</p>
<p>// between … and …<br>判断属性值是否在between范围内，是返回1，不是返回0<br>select age between 1 and 2 , sex between ‘a’ and ‘z’ from mylist;<br>not between … and …</p>
<p>// in<br>select age in (1,2) from mylist;</p>
<p>// &amp;&amp; and 与<br>所有操作数不能为0和NULL，有的话返回0和null<br>-1&amp;&amp;2 : 返回1 ，2边都是true<br>0&amp;&amp;3: 返回0<br>0&amp;&amp;NULL：返回0<br>3&amp;&amp;NULL：返回NULL</p>
<p>// ||  or 或<br>所有操作数中，只要有一个数不是0或null的返回1，全是null和0的返回 null，全是的返回0<br>3|| null ： 返回3<br>3|| 0： 返回3<br>3|| null ||0 返回3<br>null || 0 ： 返回 null，<br>null || null ： =》 null，<br>0 || 0 =》 0；<br>//! 非运算<br>!1 =&gt; 0<br>!-1 =&gt; 0<br>!0 =&gt; 1<br>!null =&gt; null</p>
<p>// &amp; 位与计算<br>5&amp;6 =&gt; 4<br>select 5&6;<br>直接查询&amp;与预算的结果<br>// | 位或计算<br>select 5|6;<br>// ~ 位取反<br>select ~1;</p>
<p>//BIN():查看二进制<br>select bin(-1);</p>
<p>//&lt;&lt;,&gt;&gt;<br>select 5&lt;&lt;1,5&gt;&gt;1;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//函数<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>rand() : 返回0~1的随机数<br>round(x):返回离x最近的整数<br>round(x,y): 保留x小数点y位，截取时四舍五入</p>
<p>select rand();</p>
<p>//字符串函数<br>trim(s): 去掉开始和结尾的空格<br>length(s): 字符的长度<br>concat(s1，s2):将s1和s2合并成一个新的字符串<br>concat(x，s1,s2):在s1和s2链接时，中间插入x字符 ： abc-cda<br>substring(s,n,len)：从n开始截取len长度字符<br>position(s1 in s) : 返回s1 在字符串s中的位置(1~n),不是下标位置，indexof<br>strcmp(s1,s2): 比较字符串大小, 1大于，0相等，-1小于<br>left(str,len): 获取左边前len位字符<br>right(str,len):<br>format():数字格式化 help format查看帮助</p>
<p>//日期时间<br>curdate():当前日期<br>curtime():当前时间<br>select curdate(),curtime();</p>
<p>now(): 当前日期和时间<br>current_timestamp():当前时间轴</p>
<p>//unix时间戳函数<br>unix_timestamp() : 将当前时间格式转换成int整形数值<br>unix_timestamp(dt): 将dt时间格式转换成int值<br>select unix_timestamp(‘2005-1-2 1:0:0’);</p>
<p>from_unixtime(1400023232):将int型时间值转换日期时间格式: 20012-1-1 1:0:0</p>
<p>//条件判断函数</p>
<p>if(exp, r1,r2):<br>=&gt; a==1？r1:r2;<br>假如条件exp=true,返回r1,或者返回r2;<br>select if( 1!=2,1,0);<br>select age ， if(age &gt;2, ‘ok’,’no’) from mylist;<br>表示从mylist表中查询age记录，如果&gt;2,就输出ok</p>
<p>ifnull(r1,r2)<br>r1 != null? r1:r2<br>假如r1不为null，返回r1</p>
<p>//case… end 函数<br>select age, case when age =1 then ‘Y’ when age =2 then ‘N’ else ‘O’ end alias_name from mylist;</p>
<p>//系统函数<br>database():当前数据名<br>select database();<br>version():数据库版本<br>connection_id():当前服务器的连接数<br>user():当前用户</p>
<p>//加密函数<br>md5(s)<br>select md5(‘aaaa’);</p>
<p>encode(s1,key):用key字符串来加密s1,返回的是二进制数据，必须用BLOB类型来保存<br>decode(s2,key)：解密，s2是encode加密后的二进制</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//存储过程和函数<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>存储过程和函数，是在数据库中定义一些sql语句的集合体，方便重复调用避免重复编写相同sql语句，<br>相当于api封装<br>并且，存储过程和函数是在mysql服务器中存储 和执行的，可以减少客服端和服务端的数据传输</p>
<p>//创建存储过程 create procedure<br>//in ,out ,inout : 输入参数，输出参数，输入输出参数<br>// reads sql data: 表示子程序中包含读数据的语句<br>// modifies sql data: 表示子程序包含写数据的语句<br>// no sql: 子程序不包含sql语句<br>// contains sql: 包含sql语句，但不包含读或写数据的语句</p>
<p>create procudure pro_name (in v1 int , out v2 int, inout v3 int)<br>reads sql data<br>begin<br>select count(*) into v2 from listname where id = v1;<br>end</p>
<hr>
<p>delimiter &amp;&amp;</p>
<p>//创建存储函数 create function<br>returns 类型</p>
<p>create function fun_name(pare int)<br>returns varchar(30)<br>begin<br>return (select name from employee where id=pare);<br>end</p>
<p>//定义变量 :declare<br>declare value_name int default 0;<br>//变量赋值<br>set value_name = 1;<br>select 1 INTO VALUE_NAME；<br>select d_id into value_name from listname where id=2;<br>//调用存储过程和函数<br>call fun_name(inputname,@outname)<br>call num_from_employee(1022,@n);<br>select @n; //输出@n获得的数据</p>
<p>//调用函数<br>select name_from_employee(3);<br>//查看存储过程和函数<br>show procedure status like ‘employee_name‘；<br>show create procedure employ_name\G;<br>show create function  name\G;<br>select * from information_schema.Routines where ROUTINE_NAME = ‘name’;<br>//修改<br>alter procedure name;<br>alter function name；<br>//删除<br>drop procedure name;<br>drop function name;</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//用户管理<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//数据备份和还原<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>不能在mysql命了下执行，中是操作系统命令，cmd<br>//备份 :mysqldump<br>mysqldump -u root -p databasename listname1 , listname2 &gt; c:\copy.sql;<br>//备份数据下所有的表<br>mysqldump -u root -p databasename &gt; c:xxx.sql （不要带;)<br>//备份多个数据库<br>mysqldump -u root -p –databases dbname1, dbname2 &gt; c:xx.sql</p>
<p>//备份所有数据库<br>mysqldump -u root -p –all-databases &gt; c:\xxx.sql</p>
<p>//还原<br>mysql -u root -p dataname &lt; c:\bak.sql<br>//不指定数据库名时候，需要从备份的所有数据库文件中还原。<br>mysql -u root -p &lt; c:all.sql</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//17 mysql日志<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>二进制日志：以二进制文件的形式记录了数据库的操作insert,update,delete,create，但不记录查询语句select<br>错误日志: 记录服务器的启动，关闭和运行错误等信息<br>通用查询日志: 记录用户登录和查询的信息<br>慢查询日志：记录执行时间超过指定时间的操作</p>
<p>//二进制日志<br>linux: my.cnf, windows: my.ini<br>[mysqld]<br>log-bin=C:\log\mysql-bin<br>可以在mysql的data目录下生成二进制文件。必须重启mysqlserver56服务才有效。<br>C:\大写<br>//查看二进制log</p>
<h1 id="cd-c-log"><a href="#cd-c-log" class="headerlink" title="cd c:\log\"></a>cd c:\log\</h1><h1 id="mysqlbinlog-mysql-bin-xxxxx"><a href="#mysqlbinlog-mysql-bin-xxxxx" class="headerlink" title="mysqlbinlog mysql-bin.xxxxx"></a>mysqlbinlog mysql-bin.xxxxx</h1><p>每次执行insert等语句后，会自动更新log文件，保存对应的语句到log上。</p>
<p>//删除所有二进制log文件<br>mysql&gt; reset master;</p>
<p>//恢复数据库<br>mysqlbin-log mylog-bin.000001 | mysql -u root -p<br>恢复必须是从小编号0000001开始到大编号一个一个恢复</p>
<p>//暂停二进制日志功能<br>set sql_log_bin=0<br>set sql_log_bin=1 //开启<br>//刷新log<br>mysqladmin -u root -p flush-logs</p>
<p>///////错误日志<br>记录mysql服务的开启，关闭和错误信息，服务无法启动等原因可以通过此查看到原因<br>默认开启，无法禁止<br>[mysqld]</p>
<h1 id="Error-Logging"><a href="#Error-Logging" class="headerlink" title="Error Logging."></a>Error Logging.</h1><p>log-error=”XHY-PC.err”</p>
<p>///////通用查询日志<br>记录用户所有的操作，包括启动，关闭mysql服务，desc ,update，select等<br>log-output=FILE<br>general-log=1<br>general_log_file=”XHY-PC.log”</p>
<hr>
<p>//删除通用日志<br>mysqladmin -u root -p flush-logs<br>创建新日志，覆盖久日志</p>
<p>///////慢查询日志<br>记录执行时间超过指定时间的查询语句。<br>slow-query-log=1<br>slow_query_log_file=”XHY-PC-slow.log”<br>long_query_time=5  //查询等待最大时间秒<br>会在log中记录那条语句查询时间超过了设定的时间</p>
<h1 id="Query-time-7-357420-Lock-time-0-000000-Rows-sent-1-Rows-examined-0"><a href="#Query-time-7-357420-Lock-time-0-000000-Rows-sent-1-Rows-examined-0" class="headerlink" title="Query_time: 7.357420  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0"></a>Query_time: 7.357420  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0</h1><p>use test;<br>SET timestamp=1483545180;<br>select benchmark(200000000,1*2);</p>
<p>//删除日志<br>mysqladmin -u root -p flush-logs<br>创建新日志，覆盖久日志</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//18 mysql日志<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>优化查询速度：<br>使用索引index,可以减少查询数目，加快速度<br>条件，多字段索引时，必须第一个字段产于进来，第2个字段才有效。<br>or：<br>必须两边条件判断都是索引字段才有效。<br>like ，以%开头无效，必须’xia%‘</p>
<p>//优化插入速度：<br>insert数据时，索引，唯一性校验都会影响插入记录的速度。<br>并且，一次插入多条记录快于多次插入记录</p>
<p>//禁用索引<br>插入记录时，mysql会根据表的所有对插入的记录排序<br>alter table mylist disable keys;<br>alter table mylist enable keys;</p>
<p>//禁用唯一性检查<br>插入数据时，mysql会对插入的记录进行唯一性校验。<br>我们在可以在插入完后再开启<br>set unique_ckecks=0；</p>
<p>//分析表:分析关键字的分布<br>analyze table mylist;<br>//检查表: 检查表是否存在错误<br>check table mylist;<br>//优化表: 消除删除和更新造成的空间浪费<br>只能优化varchar,blob,text类型字段<br>optimize table mylist；</p>
<p>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>//20 C#连接mysql<br>//<strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong><strong>*****</strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong></strong><br>mysql.data.dll<br>using Mysql.Data.MysqlClient;</p>
<p>1:连接数据库,ip，用户名，密码<br>MySqlConnection conn<br>= new MySqlConnection(“Host=localhost;Database=mydata;Username=root;Pwd=123456”);<br>host:主机，或ip<br>database：数据库<br>username，pwd: 用户名，密码<br>2：连接数据库:open<br>conn.open();<br>conn.OpenAsync()://异步开始连接<br>conn.State: 连接状态：close，open<br>最好用异步的连接，可以返回连接失败</p>
<p>3：对数据库插入，更新，删除:MysqlCommand<br> MySqlCommand com = new MySqlCommand(@”insert into mylist(id,name) values(6,’bbb’);”, conn);<br> com.ExecuteNonQuery();</p>
<p>MySqlCommand com = new MySqlCommand();<br>com.CommandText  = @”update mylist set name=’hai’ where id=2;”;<br>com.CommandText += @”delete from mylist where id=5;”; //执行多个<br>com.Connection = conn;<br>com.ExecuteNonQuery(); //执行</p>
<p>ExecuteNonQuery：无法执行select语句。</p>
<p>4：使用select语句查询数据<br>ExecuteReader: 执行select语句，返回MySqlDataReader数据结构，不会自动释放资源<br>Read: 读取当前结果的数据，返回true，false</p>
<p>com.CommandText = @”select * from mylist where id=2;”;<br>MySqlDataReader dr = com.ExecuteReader();<br>while(dr.read()){<br>    dr[“id”], dr[“name”]<br>}<br>dr[“id”]: 表示读取id字段的值。<br>dr.read()：每次只能读取一条记录，直到false，注意，read只能读取一个select语句的结果。<br>        如果执行多个select，则需 NextResult(),读取下一个select结果<br>5：一次执行多个select语句<br>NextResult：取得下一个执行select的结果，然后用read读取里面的数据</p>
<p>com.CommandText = @”select * from mylist ;”;<br>com.CommandText += @”select * from mylist1 ;”;<br>MySqlDataReader dr = com.ExecuteReader();<br>if(dr.NextResult())<br>{<br>    while (dr.Read())<br>    {<br>        Console.WriteLine(dr[“id”] + “,” + dr[“name”] + “,” + dr[“sex”] + “,” + dr[“age”]);<br>    }<br>}<br>6：Select语句只返回一个值:<br>ExecuteScalar:只返回一个值object，所使用资源比executeReader少很多。会自动释放资源<br>com.CommandText = @”select id from mylist where id =1;”;<br>int id = (int)com.ExecuteScalar();<br>7：处理带参数的sql语句<br>//执行单个参数语句<br>com.commandText =@”insert into mylist(id,name) values(?name1,?name2)”;<br>com.parameters.Add(“?name1”,1);<br>com.parameters.Add(“?name2”,”ccc”);<br>com.ExecuteNonQuery();<br>?name1: 参数变量名称<br>//执行多个参数语句<br>MysqlParameter :多次给此类型赋值即可实现<br>//声明一个参数变量p_id;<br>MysqlParameter p_id   = com.Parameters.Add(“?idvalue”, MySqlDbType.Int32);<br>p_name = com.Parameters.AddWithValue(“?namevalue”, MySqlDbType.VarChar);<br>//执行一条insert语句<br>        //准备多次赋值<br>       com.Prepare();<br>                p_id.Value = 9;<br>                p_name.Value = “ddd”;<br>                com.ExecuteNonQuery();<br>            //再次执行赋值<br>            p_id.Value = 10;<br>                p_name.Value = “eee”;<br>                com.ExecuteNonQuery();<br>8.使用DataSet对象和DataTable对象<br>一个DataSet对象可以管理多个DataTable对象<br>Using System.Data;<br>用MysqlDataAdapter的Fill来执行select语句<br>      com.CommandText = @”select * from mylist;”;<br>      MySqlDataAdapter da = new MySqlDataAdapter(com);<br>      DataSet ds = new DataSet();<br>      DataTable dt = new DataTable();<br>      da.Fill(ds,”mylist0”); //执行select语句，将查询结果保存dataset里面。<br>      dt = ds.Tables[“mylist0”]; //dt来读取查询的结果。<br>      //将查询的多条记录都输出<br>          for(int j=0;j&lt;dt.Rows.Count;j++)<br>                {<br>                    Console.WriteLine(dt.Rows[j][“id”]+”,”+dt.Rows[j][“name”]);<br>                }</p>
<p>9.关闭创建的对象<br>MysqlConnect conn.close();<br>MysqlDataReader dr.close();</p>
<p>10.C#备份和还原mysql数据库<br>原理一样是执行mysqldump命令，需要process类调用外部命令</p>
<p>ProcessStartInfo:指定了启动进程的资源<br>ProcessStartInfo.FileName :设置需要启动的程序<br>ProcessStartInfo.Argument：设置程序需要的参数<br>              ProcessStartInfo ps = new ProcessStartInfo();<br>                ps.FileName = “cmd.exe”;<br>                ps.Arguments = @”/c mysqldump -h localhost -u root -p123456 test mylist &gt; c:/bak.sql”;<br>                Process.Start(ps);<br>/c :执行完就退出<br>Process.Start(“cmd.exe”,”/c mysqldump -h localhost -u root -p123456 test mylist &gt; c:/bak.sql”);</p>
<p>还原：<br>    ps.Arguments = @”/c mysql -h localhost -u root -p123456 test  &lt; c:/bak.sql”;</p>

        </div>
        
        <div class="level is-size-7 is-uppercase">
            <div class="level-start">
                <div class="level-item">
                    <span class="is-size-6 has-text-grey has-mr-7">#</span>
                    <a class="has-link-grey -link" href="/mystudy/tags/学习/">学习</a>
                </div>
            </div>
        </div>
        
        

        
        <div class="social-share"></div>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js@1.0.16/dist/css/share.min.css">
<script src="https://cdn.jsdelivr.net/npm/social-share.js@1.0.16/dist/js/social-share.min.js"></script>
        
    </div>
</div>





<div class="card card-transparent">
    <div class="level post-navigation is-flex-wrap is-mobile">
        
        <div class="level-start">
            <a class="level level-item has-link-grey  article-nav-prev" href="/mystudy/2020/09/02/unity-显示所有可移动网格点/">
                <i class="level-item fas fa-chevron-left"></i>
                <span class="level-item">unity-显示所有可移动网格点</span>
            </a>
        </div>
        
        
        <div class="level-end">
            <a class="level level-item has-link-grey  article-nav-next" href="/mystudy/2020/07/27/人工智能12-寻路/">
                <span class="level-item">人工智能12-寻路</span>
                <i class="level-item fas fa-chevron-right"></i>
            </a>
        </div>
        
    </div>
</div>


</div>
                




<div class="column is-4-tablet is-4-desktop is-3-widescreen  has-order-1 column-left ">
    
        
<div class="card widget">
    <div class="card-content">
        <nav class="level">
            <div class="level-item has-text-centered" style="flex-shrink: 1">
                <div>
                    
                        <img class="image is-128x128 has-mb-6" src="/mystudy/images/avatar2.jpg" alt="满头大汉老黄牛">
                    
                    
                    <p class="is-size-4 is-block">
                        满头大汉老黄牛
                    </p>
                    
                    
                    <p class="is-size-6 is-block">
                        游戏
                    </p>
                    
                    
                    <p class="is-size-6 is-flex is-flex-center has-text-grey">
                        <i class="fas fa-map-marker-alt has-mr-7"></i>
                        <span>火星</span>
                    </p>
                    
                </div>
            </div>
        </nav>
        <nav class="level is-mobile">
            <div class="level-item has-text-centered is-marginless">
                <div>
                    <p class="heading">
                        文章
                    </p>
                    <p class="title has-text-weight-normal">
                        70
                    </p>
                </div>
            </div>
            <div class="level-item has-text-centered is-marginless">
                <div>
                    <p class="heading">
                        分类
                    </p>
                    <p class="title has-text-weight-normal">
                        10
                    </p>
                </div>
            </div>
            <div class="level-item has-text-centered is-marginless">
                <div>
                    <p class="heading">
                        标签
                    </p>
                    <p class="title has-text-weight-normal">
                        3
                    </p>
                </div>
            </div>
        </nav>
        <div class="level">
            <a class="level-item button is-link is-rounded" href="https://github.com/koliy/" target="_blank">
                关注我</a>
        </div>
        
        
        <div class="level is-mobile">
            
            <a class="level-item button is-white is-marginless" target="_blank"
                title="Github" href="https://github.com/koliy/">
                
                <i class="fab fa-github"></i>
                
            </a>
            
        </div>
        
    </div>
</div>
    
        


    
        
<div class="card widget">
    <div class="card-content">
        <div class="menu">
            <h3 class="menu-label">
                分类
            </h3>
            <ul class="menu-list">
            <li>
        <a class="level is-marginless" href="/mystudy/categories/3D数学基础-图形与游戏开发/">
            <span class="level-start">
                <span class="level-item">3D数学基础:图形与游戏开发</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">3</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/AI/">
            <span class="level-start">
                <span class="level-item">AI</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">12</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/MySql/">
            <span class="level-start">
                <span class="level-item">MySql</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">1</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/SurfaceShader/">
            <span class="level-start">
                <span class="level-item">SurfaceShader</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">1</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/Unity/">
            <span class="level-start">
                <span class="level-item">Unity</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">1</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/git/">
            <span class="level-start">
                <span class="level-item">git</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">3</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/shade学习/">
            <span class="level-start">
                <span class="level-item">shade学习</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">33</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/xlua/">
            <span class="level-start">
                <span class="level-item">xlua</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">1</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/网络同步/">
            <span class="level-start">
                <span class="level-item">网络同步</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">10</span>
            </span>
        </a></li><li>
        <a class="level is-marginless" href="/mystudy/categories/设计模式/">
            <span class="level-start">
                <span class="level-item">设计模式</span>
            </span>
            <span class="level-end">
                <span class="level-item tag">5</span>
            </span>
        </a></li>
            </ul>
        </div>
    </div>
</div>
    
        <div class="card widget">
    <div class="card-content">
        <div class="menu">
        <h3 class="menu-label">
            归档
        </h3>
        <ul class="menu-list">
        
        <li>
            <a class="level is-marginless" href="/mystudy/archives/2020/09/">
                <span class="level-start">
                    <span class="level-item">九月 2020</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">1</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/mystudy/archives/2020/07/">
                <span class="level-start">
                    <span class="level-item">七月 2020</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">23</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/mystudy/archives/2020/06/">
                <span class="level-start">
                    <span class="level-item">六月 2020</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">17</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/mystudy/archives/2020/03/">
                <span class="level-start">
                    <span class="level-item">三月 2020</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">2</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/mystudy/archives/2020/02/">
                <span class="level-start">
                    <span class="level-item">二月 2020</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">4</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/mystudy/archives/2019/09/">
                <span class="level-start">
                    <span class="level-item">九月 2019</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">5</span>
                </span>
            </a>
        </li>
        
        <li>
            <a class="level is-marginless" href="/mystudy/archives/2019/08/">
                <span class="level-start">
                    <span class="level-item">八月 2019</span>
                </span>
                <span class="level-end">
                    <span class="level-item tag">18</span>
                </span>
            </a>
        </li>
        
        </ul>
        </div>
    </div>
</div>
    
        
<div class="card widget">
    <div class="card-content">
        <h3 class="menu-label">
            标签云
        </h3>
        <a href="/mystudy/tags/game/" style="font-size: 15px;">game</a> <a href="/mystudy/tags/git/" style="font-size: 10px;">git</a> <a href="/mystudy/tags/学习/" style="font-size: 20px;">学习</a>
    </div>
</div>

    
    
        <div class="column-right-shadow is-hidden-widescreen is-sticky">
        
            
<div class="card widget" id="toc">
    <div class="card-content">
        <div class="menu">
            <h3 class="menu-label">
                目录
            </h3>
            <ul class="menu-list"><ul class="menu-list"><li>
        <a class="is-flex" href="#mysql">
        <span class="has-mr-6">1.1</span>
        <span>mysql</span>
        </a></li></ul><li>
        <a class="is-flex" href="#cd-c-log">
        <span class="has-mr-6">2</span>
        <span>cd c:\log\</span>
        </a></li><li>
        <a class="is-flex" href="#mysqlbinlog-mysql-bin-xxxxx">
        <span class="has-mr-6">3</span>
        <span>mysqlbinlog mysql-bin.xxxxx</span>
        </a></li><li>
        <a class="is-flex" href="#Error-Logging">
        <span class="has-mr-6">4</span>
        <span>Error Logging.</span>
        </a></li><li>
        <a class="is-flex" href="#Query-time-7-357420-Lock-time-0-000000-Rows-sent-1-Rows-examined-0">
        <span class="has-mr-6">5</span>
        <span>Query_time: 7.357420  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0</span>
        </a></li></ul>
        </div>
    </div>
</div>

        
        </div>
    
</div>

                




<div class="column is-4-tablet is-4-desktop is-3-widescreen is-hidden-touch is-hidden-desktop-only has-order-3 column-right is-sticky">
    
        
<div class="card widget" id="toc">
    <div class="card-content">
        <div class="menu">
            <h3 class="menu-label">
                目录
            </h3>
            <ul class="menu-list"><ul class="menu-list"><li>
        <a class="is-flex" href="#mysql">
        <span class="has-mr-6">1.1</span>
        <span>mysql</span>
        </a></li></ul><li>
        <a class="is-flex" href="#cd-c-log">
        <span class="has-mr-6">2</span>
        <span>cd c:\log\</span>
        </a></li><li>
        <a class="is-flex" href="#mysqlbinlog-mysql-bin-xxxxx">
        <span class="has-mr-6">3</span>
        <span>mysqlbinlog mysql-bin.xxxxx</span>
        </a></li><li>
        <a class="is-flex" href="#Error-Logging">
        <span class="has-mr-6">4</span>
        <span>Error Logging.</span>
        </a></li><li>
        <a class="is-flex" href="#Query-time-7-357420-Lock-time-0-000000-Rows-sent-1-Rows-examined-0">
        <span class="has-mr-6">5</span>
        <span>Query_time: 7.357420  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0</span>
        </a></li></ul>
        </div>
    </div>
</div>

    
    
</div>

            </div>
        </div>
    </section>
    <footer class="footer">
    <div class="container1">
        <div class="level1">
            <div class="level-start1 has-text-centered-mobile1">
                <a class="footer-logo is-block has-mb-6" href="/mystudy/">
                

                
                </a>
                <p class="is-size-7">
                &copy; 2020 Koliy&nbsp;
                Powered by <a href="https://github.com/koliy/koliy.github.io" target="_blank">Hexo</a> & <a
                        href="https://github.com/ppoffice/hexo-theme-icarus" target="_blank">Icarus</a>
                
                <br>
                <span id="busuanzi_container_site_uv">
                共<span id="busuanzi_value_site_uv">0</span>个访客
                </span>
                
                </p>
            </div>
            <div class="level-end1">
            
            </div>
        </div>
    </div>
</footer>
    <script src="https://cdn.jsdelivr.net/npm/jquery@3.3.1/dist/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/moment@2.22.2/min/moment-with-locales.min.js"></script>
<script>moment.locale("zh-CN");</script>

<script>
var IcarusThemeSettings = {
    article: {
        highlight: {
            clipboard: true,
            fold: 'unfolded'
        }
    }
};
</script>


    <script src="https://cdn.jsdelivr.net/npm/clipboard@2.0.4/dist/clipboard.min.js" defer></script>



    
    
    
    <script src="/mystudy/js/animation.js"></script>
    

    
    
    
    <script src="https://cdn.jsdelivr.net/npm/lightgallery@1.6.8/dist/js/lightgallery.min.js" defer></script>
    <script src="https://cdn.jsdelivr.net/npm/justifiedGallery@3.7.0/dist/js/jquery.justifiedGallery.min.js" defer></script>
    <script src="/mystudy/js/gallery.js" defer></script>
    

    
    

<div id="outdated">
    <h6>Your browser is out-of-date!</h6>
    <p>Update your browser to view this website correctly. <a id="btnUpdateBrowser" href="http://outdatedbrowser.com/">Update
            my browser now </a></p>
    <p class="last"><a href="#" id="btnCloseUpdateBrowser" title="Close">&times;</a></p>
</div>
<script src="https://cdn.jsdelivr.net/npm/outdatedbrowser@1.1.5/outdatedbrowser/outdatedbrowser.min.js" defer></script>
<script>
    document.addEventListener("DOMContentLoaded", function () {
        outdatedBrowser({
            bgColor: '#f25648',
            color: '#ffffff',
            lowerThan: 'flex'
        });
    });
</script>


    
    
<script src="https://cdn.jsdelivr.net/npm/mathjax@2.7.5/unpacked/MathJax.js?config=TeX-MML-AM_CHTML" defer></script>
<script>
document.addEventListener('DOMContentLoaded', function () {
    MathJax.Hub.Config({
        'HTML-CSS': {
            matchFontHeight: false
        },
        SVG: {
            matchFontHeight: false
        },
        CommonHTML: {
            matchFontHeight: false
        },
        tex2jax: {
            inlineMath: [
                ['$','$'],
                ['\\(','\\)']
            ]
        }
    });
});
</script>

    
    

<a id="back-to-top" title="回到顶端" href="javascript:;">
    <i class="fas fa-chevron-up"></i>
</a>
<script src="/mystudy/js/back-to-top.js" defer></script>


    
    

    
    
    
    

    
    
    
    
    

    


<script src="/mystudy/js/main.js" defer></script>

    
    <div class="searchbox ins-search">
    <div class="searchbox-container ins-search-container">
        <div class="searchbox-input-wrapper">
            <input type="text" class="searchbox-input ins-search-input" placeholder="想要查找什么..." />
            <span class="searchbox-close ins-close ins-selectable"><i class="fa fa-times-circle"></i></span>
        </div>
        <div class="searchbox-result-wrapper ins-section-wrapper">
            <div class="ins-section-container"></div>
        </div>
    </div>
</div>
<script>
    (function (window) {
        var INSIGHT_CONFIG = {
            TRANSLATION: {
                POSTS: '文章',
                PAGES: '页面',
                CATEGORIES: '分类',
                TAGS: '标签',
                UNTITLED: '(无标题)',
            },
            CONTENT_URL: '/mystudy/content.json',
        };
        window.INSIGHT_CONFIG = INSIGHT_CONFIG;
    })(window);
</script>
<script src="/mystudy/js/insight.js" defer></script>
<link rel="stylesheet" href="/mystudy/css/search.css">
<link rel="stylesheet" href="/mystudy/css/insight.css"><!-- hexo-inject:begin --><!-- Begin: Injected MathJax -->
<script type="text/x-mathjax-config">
  MathJax.Hub.Config({"tex2jax":{"inlineMath":[["$","$"],["\\(","\\)"]],"skipTags":["script","noscript","style","textarea","pre","code"],"processEscapes":true},"TeX":{"equationNumbers":{"autoNumber":"AMS"}}});
</script>

<script type="text/x-mathjax-config">
  MathJax.Hub.Queue(function() {
    var all = MathJax.Hub.getAllJax(), i;
    for(i=0; i < all.length; i += 1) {
      all[i].SourceElement().parentNode.className += ' has-jax';
    }
  });
</script>

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js">
</script>
<!-- End: Injected MathJax -->
<!-- hexo-inject:end -->
    
</body>
</html>